using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;
using System.Reflection;
using System.Security;
using System.Security.Cryptography;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Replication;

namespace THSLib
{
        public  class SqlDbHelper
        {
            #region --constant--
            private static SqlDatabase database = null;
            public static String AAStatusColumn = "AAStatus";
            private static SortedList lstPrimaryColumns = new SortedList();
            private static List<String> lstTableColumnNames = new List<String>();
            private static String _connectionString = String.Empty;
            private static String _companyName = String.Empty;
            private static MessageStatus _synchronizationStatus;
            public static string TableName = string.Empty;
            #endregion
       
           #region --Properties--
            public static SortedList PrimaryColumnList
			           {
                get { return lstPrimaryColumns; }
            }
        

        public static MessageStatus SynchronizationStatus
        {
            get
            {
                return _synchronizationStatus;
            }
            set
            {
                _synchronizationStatus = value;
            }
        }
        public static String CompanyName
        {
            get { return _companyName; }
            set { _companyName = value; }
        }
        #endregion

        #region "Constructor"
        static SqlDbHelper()
        {
            try
            {
                _connectionString = GetConnectionString();
                //If can get connection string from registry
                if (!String.IsNullOrEmpty(_connectionString))
                    database = new SqlDatabase(_connectionString);
                else
                {
                    //Get from configuration file
                    _connectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
                    database = (SqlDatabase)DatabaseFactory.CreateDatabase();
                }
                lstPrimaryColumns= GetAllTablePrimaryColumns();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region --Connection--

        public static String GetConnectionString()
        {
            String result = String.Empty;
            RegistryWorker rw = new RegistryWorker();
            rw.SubKey = "SOFTWARE\\THS";
            rw.SubKey = rw.SubKey + "\\ConnectionString";
            String strDatabase = rw.Read("Database");
            String strServer = rw.Read("Server");
            String strUser = rw.Read("User");
            String strPassword = rw.Read("Password");
            EnCrypto enCrypt = new EnCrypto();
            strPassword = enCrypt.Decrypt(strPassword);
            return result = String.Format("Data Source ={0};Initial Catalog={1};User={2};Password={3}", strServer, strDatabase, strUser, strPassword);
        }

        public static String GetConnectionString(String strCompanyName)
        {
            _companyName = strCompanyName;

            RegistryWorker rw = new RegistryWorker();
            if (!String.IsNullOrEmpty(strCompanyName))
                rw.SubKey = "SOFTWARE\\THS\\ConnectionString\\" + strCompanyName;
            else
                rw.SubKey = "SOFTWARE\\THS\\ConnectionString";
            String strDatabase = rw.Read("Database");
            String strServer = rw.Read("Server");
            String strUser = rw.Read("User");
            string strPassword = rw.Read("Password");

            EnCrypto cryp = new EnCrypto();
            strPassword = cryp.Decrypt(strPassword);
            return string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", strServer, strDatabase, strUser, strPassword);

        }
        /// <summary>
        ///   this function to change connection by compay name
        /// </summary>
        public static void ChangeCompanyConnection(String strCompanyName)
        {
            String strConnection = GetConnectionString(strCompanyName);
            if (String.IsNullOrEmpty(_connectionString))
                _connectionString = strConnection;

            _companyName = strCompanyName;
            database = new SqlDatabase(strConnection);
            lstPrimaryColumns = GetAllTablePrimaryColumns();
        }

        public static void SwitchConnection(String strConnectionString)
        {
               database = new SqlDatabase(strConnectionString);
        }

        public static bool TestConnection()
        {
            try
            {
                TableName = "CSCompanys";
                String strTestQuery = "Select * From [CSCompanys]";
                DbCommand cmd = database.GetSqlStringCommand(strTestQuery);
                database.ExecuteDataSet(cmd);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        ///   this function rollback to local connection
        /// </summary>
        /// 
        public static void RollBackLocalConnection()
        {
            database = new SqlDatabase(_connectionString);
        }
        #endregion

        #region "Get Object Functions"
        /// <summary>
        ///   get object
        /// </summary>
        /// 
        public static object GetSingleObject(DataTable dt, Type type)
        {
            try
            {
                if (dt.Rows.Count <= 0)
                    return null;
                System.Reflection.PropertyInfo[] properties = type.GetProperties();
                object obj = new object();
                if (Convert.ToInt32(dt.Rows[0][0]) == 0)
                {
                    obj = GetObjectFromDataRow(dt.Rows[1], type);
                }
                else
                {
                    obj = GetObjectFromDataRow(dt.Rows[0], type);
                }
                return obj;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public static System.Collections.ArrayList GetObjectCollection(DataTable dt, Type type, string tableName)
        {
            System.Reflection.PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic);
            System.Collections.ArrayList list = new System.Collections.ArrayList();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(GetObjectFromDataRow(row, type));
            }
            return list;
        }
            /// <summary>
            /// get object from Data row 
            /// </summary>
            /// <param name="row"></param>
            /// <param name="type"></param>
            /// <returns></returns>
            /// 

        public static object GetObjectFromDataRow(DataRow row, Type type)
        {
            object obj = type.InvokeMember("", System.Reflection.BindingFlags.CreateInstance, null, null, null);
            foreach (DataColumn column in row.Table.Columns)
            {
                object objValue = row[column];
                if (objValue != System.DBNull.Value)
                {
                    PropertyInfo property = obj.GetType().GetProperty(column.ColumnName);
                    if (property != null)
                        property.SetValue(obj, objValue, null);
                }
            }
            return obj;
        }        

        public static DataSet GetObjectbyId(string tableName, int id)
        {
            TableName = tableName;
            String sqlCommand = string.Format("Select * From {0} where {1} = {2}", GetPrimaryKeyColumn(tableName), id);
            DbCommand cmd = database.GetSqlStringCommand(sqlCommand);
            return database.ExecuteDataSet(cmd);
        }

        public static void SetValueToPrimaryColumn(object obj, DALBaseProvider provider, int iObjectID)
        {
            TableName = provider.TableName;
            string strPrimaryKeyColumn = GetPrimaryKeyColumn(provider.TableName);            
            
            PropertyInfo property = provider.ObjectType.GetProperty(strPrimaryKeyColumn);
            property.SetValue(obj, iObjectID, null);

        }

        public static void SetValueToIDStringColumn(object obj, DALBaseProvider provider, int iObjectID)
        {
            TableName = provider.TableName;
            string strPrimaryKeyColumn = GetPrimaryKeyColumn(provider.TableName);            
            PropertyInfo property = provider.ObjectType.GetProperty(strPrimaryKeyColumn + "String");
            if (property != null)
            {
                property.SetValue(obj, iObjectID.ToString(), null);
            }
        }
        

        public static object GetPrimaryColumnValue(object obj, DALBaseProvider provider)
        {
            TableName = provider.TableName;
            string strPrimaryKeyColumn = GetPrimaryKeyColumn(provider.TableName);
            PropertyInfo property = provider.ObjectType.GetProperty(strPrimaryKeyColumn);
            object objValue = property.GetValue(obj, null);
            return objValue;
        }

        #endregion

        #region Table,Column,Query Helpers
        /// <summary>
        /// get primary column name by table name
        /// </summary>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        /// 
        public static string GetPrimaryKeyColumn(string strTableName)
        {
            //DataSet ds = database.ExecuteDataSet("GEDbUtil_SelectTablePrimaryKeys", strTableName);
            //if (ds.Tables.Count > 0)
            //{
            //    if (ds.Tables[0].Rows.Count > 0)
            //        return ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString();
            //}
            //return string.Empty;

            String strPrimaryKeyColumn = String.Empty;
            TableName = strTableName;
            if (lstPrimaryColumns[strTableName] != null)
                strPrimaryKeyColumn = lstPrimaryColumns[strTableName].ToString();
            else
            {
                String strQuery = "SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ";
                strQuery += String.Format("WHERE (kcu.TABLE_NAME='{0}')", strTableName);
                strQuery += "AND(kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)AND(tc.CONSTRAINT_TYPE='PRIMARY KEY')";
                DbCommand cmd = GetQuery(strQuery);
                DataSet ds = RunQuery(cmd);
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                        strPrimaryKeyColumn = ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString();
                }
            }

            return strPrimaryKeyColumn;
        }

        public static DataSet GetAllTableColumns(string strTableName)
        {
            String strQuery = String.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", strTableName);
            DbCommand cmd = GetQuery(strQuery);
            return RunQuery(cmd);
        }

        public static SortedList GetAllTablePrimaryColumns()
        {
            SortedList lstPrimaryColumns = new SortedList();
            DataSet ds = database.ExecuteDataSet("GEDbUtil_SelectAllPrimaryColumns");
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    String strTableName = row["TABLE_NAME"].ToString();
                    String strColumnName = row["COLUMN_NAME"].ToString();
                    lstPrimaryColumns.Add(strTableName, strColumnName);
                }
            }

            return lstPrimaryColumns;
        }
            /// <summary>
            ///   get table column by table name and column name
            /// </summary>
        public static DataSet GetTableColumn(String strTableName, String strColumnName)
        {
            String strQuery = String.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", strTableName, strColumnName);
            DbCommand cmd = GetQuery(strQuery);
            return RunQuery(cmd);
        }
            /// <summary>
            ///   check exists column in table by table name and column name
            /// </summary>
            /// 

        public static bool ColumnIsExistInTable(string strTableName, string strColumnName)
        {
            TableName = strTableName;
            DataSet ds = GetTableColumn(strTableName, strColumnName);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return true;
            }
            return false;
        }
            /// <summary>
            ///   check is foreign key in table by table name and column name
            /// </summary>
            /// 

        public static bool ColumnIsForeignKey(string strTableName, string strColumnName)
        {
            TableName = strTableName;
            String strQuery = String.Format("SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE kcu.TABLE_NAME = '{0}' AND kcu.COLUMN_NAME = '{1}' AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'", strTableName, strColumnName);
            DbCommand cmd = GetQuery(strQuery);
            DataSet ds = RunQuery(cmd);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return true;
            }
            return false;
        }
            /// <summary>
            ///   check column is allow null
            /// </summary>
            /// 

        public static bool IsColumnAllowNull(string strTableName, string strColumnName)
        {
            TableName = strTableName;
            DataSet ds = GetTableColumn(strTableName, strColumnName);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["IS_NULLABLE"].ToString() == "YES")
                        return true;
                    else
                        return false;
                }
            }
            return true;
        }
            /// <summary>
            ///   check is primary key of table by table name and column name
            /// </summary>
            /// 
        public static bool ColumnIsPrimaryKey(string strTableName, string strColumnName)
        {
            TableName = strTableName;
            String strQuery = String.Format("SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE kcu.TABLE_NAME = '{0}' AND kcu.COLUMN_NAME = '{1}' AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'", strTableName, strColumnName);
            DbCommand cmd = GetQuery(strQuery);
            DataSet ds = RunQuery(cmd);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                    return true;
            }
            return false;
        }
            /// <summary>
            ///   get query command by table name and table query key
            /// </summary>
            /// 

        public static String GetQueryCommandByTableNameAndTableQueryKey(String strTableName, String strTableQueryKey)
        {
            TableName = strTableName;
            String strQueryCommand = String.Empty;
            DbCommand cmd = SqlDbHelper.GetQuery(String.Format("SELECT * FROM [dbo].[STTableQueries] WHERE ([STTableQueryTableName]='{0}')AND([STTableQueryKey]='{1}')", strTableName, strTableQueryKey));
            DataSet ds = SqlDbHelper.RunQuery(cmd);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strQueryCommand = ds.Tables[0].Rows[0]["STTableQueryCommand"].ToString();
                }
            }
            return strQueryCommand;
        }
            /// <summary>
            ///   Insert query 
            /// </summary>
            /// 

        public static void InsertQueryCommand(String strQueryCommand, String strTableName, String strQueryKey)
        {
            TableName = strTableName;
            String strCommand = String.Format("INSERT INTO [dbo].[STTableQueries] ([STTableQueryID],[STTableQueryTableName],[STTableQueryKey],[STTableQueryCommand]) VALUES({0},'{1}','{2}','{3}')", GetMaxID("STTableQueries") + 1, strTableName, strQueryKey, strQueryCommand);
            DbCommand cmd = GetQuery(strCommand);
            database.ExecuteNonQuery(cmd);
        }
        #endregion

        #region "Add Parameter Functions"
        public static int GetMaxID(string tableName)
        {
            int MaxID = 0;
            TableName = tableName;
            String sqlCommand = String.Format("SELECT Max({0}) AS MaxID FROM [{1}]", GetPrimaryKeyColumn(tableName), tableName);
            DbCommand cmd = database.GetSqlStringCommand(sqlCommand);
            DataSet ds = database.ExecuteDataSet(cmd);
            if (ds.Tables.Count > 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                if (row[0].ToString() != "")
                    MaxID = (int)row[0];
            }
            return MaxID;
        }        

        public static void AddInParameter(DbCommand cmd, string name, SqlDbType type, Object objValue)
        {
            database.AddInParameter(cmd, name, type, objValue);
        }


        private static bool ColumnIsExistInBaseBusinessObject(String strPropertyName)
        {
            PropertyInfo[] properties = typeof(BusinessObject).GetProperties();

            foreach (PropertyInfo prop in properties)
            {
                if (prop.Name.Equals(strPropertyName))
                    return true;
            }
            return false;
        }

        public static void AddParameterForObject(object obj, DALBaseProvider provider, DbCommand cmd)
        {
            try
            {
                //Init table columns
                TableName = provider.TableName;
                lstTableColumnNames.Clear();
                DataSet ds = GetAllTableColumns(provider.TableName);
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                        lstTableColumnNames.Add(row["COLUMN_NAME"].ToString());
                }

                System.Reflection.PropertyInfo[] properties = provider.ObjectType.GetProperties();
                for (int i = 0; i < properties.Length; i++)
                {
                    //Add property exist in Table
                    if (lstTableColumnNames.IndexOf(properties[i].Name) >= 0)
                    {
                        object objValue = properties[i].GetValue(obj, null);
                        if (properties[i].PropertyType.Equals(typeof(Int32)))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.Int, objValue);
                        else if (properties[i].PropertyType.Equals(typeof(Boolean)))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.Bit, objValue);
                        else if (properties[i].PropertyType.Equals(typeof(short)))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.SmallInt, objValue);
                        else if (properties[i].PropertyType.Equals(typeof(double)))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.Float, objValue);
                        else if (properties[i].PropertyType.Equals(typeof(decimal)))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.Decimal, objValue);
                        else if ((properties[i].PropertyType.Equals(typeof(String))) || (properties[i].PropertyType.Equals(typeof(string))))
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.NVarChar, objValue);
                        else if (properties[i].PropertyType.Equals(typeof(DateTime)))
                        {
                            if ((DateTime)objValue == DateTime.MinValue)
                                continue;
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.DateTime, objValue);
                        }
                        else if (properties[i].PropertyType.Equals(typeof(DateTime?)))
                        {
                            if ((objValue == null) || (((DateTime)objValue) == DateTime.MinValue))
                            {
                                database.AddInParameter(cmd, properties[i].Name, SqlDbType.DateTime, null);
                            }
                            else
                            {
                                database.AddInParameter(cmd, properties[i].Name, SqlDbType.DateTime, objValue);
                            }
                        }
                        else if (properties[i].PropertyType.Equals(typeof(byte[])))
                        {
                            database.AddInParameter(cmd, properties[i].Name, SqlDbType.VarBinary, objValue);
                        }
                        else
                            continue;

                    }

                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Source + e.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        public static void AddParameterForSearchProperties(object obj, DbCommand cmd)
        {
            PropertyInfo[] properties = obj.GetType().GetProperties();
            for (int i = 0; i < properties.Length; i++)
            {
                object objValue = properties[i].GetValue(obj, null);
                String strParamName = properties[i].Name;
                if (properties[i].Name.Equals("TopResults"))
                    database.AddInParameter(cmd, strParamName, SqlDbType.Int, objValue);
                else
                {
                    if (properties[i].PropertyType.Equals(typeof(Int32)))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.Int, objValue);
                    else if (properties[i].PropertyType.Equals(typeof(Boolean)))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.Bit, objValue);
                    else if (properties[i].PropertyType.Equals(typeof(short)))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.SmallInt, objValue);
                    else if (properties[i].PropertyType.Equals(typeof(double)))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.Float, objValue);
                    else if (properties[i].PropertyType.Equals(typeof(decimal)))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.Decimal, objValue);
                    else if ((properties[i].PropertyType.Equals(typeof(String))) || (properties[i].PropertyType.Equals(typeof(string))))
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.NVarChar, objValue);
                    else if (properties[i].PropertyType.Equals(typeof(DateTime)))
                    {
                        if ((DateTime)objValue == DateTime.MinValue)
                            continue;
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.DateTime, objValue);
                    }
                    else if (properties[i].PropertyType.Equals(typeof(byte[])))
                    {
                        database.AddInParameter(cmd, properties[i].Name, SqlDbType.VarBinary, objValue);
                    }
                }
            }
        }
        #endregion

        #region "Excute Store Procedured Functions"

        public static int InsertObject(object obj, DALBaseProvider provider, string spName)
        {
            try
            {
                TableName = provider.TableName;
                DbCommand cmd = database.GetStoredProcCommand(spName);
                AddParameterForObject(obj, provider, cmd);

                database.ExecuteNonQuery(cmd);
                int ret = (int)database.GetParameterValue(cmd, GetPrimaryKeyColumn(provider.TableName));
                return ret;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Source + e.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }
        }

        public static int InsertObject(object obj, DALBaseProvider provider, string spName, DbTransaction transaction)
        {
            try
            {
                TableName = provider.TableName;
                DbCommand cmd = database.GetStoredProcCommand(spName);
                AddParameterForObject(obj, provider, cmd);
                cmd.Transaction = transaction;

                database.ExecuteNonQuery(cmd, transaction);
                transaction.Commit();

                int ret = (int)database.GetParameterValue(cmd, GetPrimaryKeyColumn(provider.TableName));
                return ret;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Source + e.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                transaction.Rollback();
                return 0;
            }
        }

        public static DataSet RunStoredProcedure(string spName)
        {
            DbCommand cmd = database.GetStoredProcCommand(spName);
            return database.ExecuteDataSet(cmd);
        }

        public static DataSet RunStoredProcedure(DbCommand cmd)
        {
            try
            {                
                return database.ExecuteDataSet(cmd);
            }
            catch (SqlException exp)
            {
                MessageBox.Show(exp.Message);
                return null;
            }
        }

        public static DataSet RunStoredProcedure(string spName, params object[] values)
        {
              return database.ExecuteDataSet(spName, values);
        }

        public static DbCommand GetStoredProcedure(string spName)
        {
            return database.GetStoredProcCommand(spName);
        }

        public static object RunStoreProcedure(DbCommand cmd, string retVariable)
        {
            database.ExecuteNonQuery(cmd);
            return (object)database.GetParameterValue(cmd, retVariable);

        }

        public static string ExecuteStoredProcedureScript(String strStoredProcedureScript)
        {
            try
            {                
                database.ExecuteNonQuery(CommandType.Text, strStoredProcedureScript);
                return "Command execute successfully!";
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                
                return e.Message;
            }
        }

      
        #endregion

        #region Transaction Functions
        public static DbTransaction BeginTransaction()
        {
            DbConnection connection = database.CreateConnection();
            return connection.BeginTransaction();
        }

        public static void CommitTransaction(DbTransaction transaction)
        {
            transaction.Commit();
        }

        public static void RollbackTransaction(DbTransaction transaction)
        {
            transaction.Rollback();
        }
        #endregion

        #region "Execute Query String"
        public static DbCommand GetQuery(String strQueryCommand)
        {
            return database.GetSqlStringCommand(strQueryCommand);
        }

        public static DbCommand GetQuery(String strTableName, String strTableQueryKey, String strQueryCommand)
        {
            TableName = strTableName;
            String strCommandFromDB = GetQueryCommandByTableNameAndTableQueryKey(strTableName, strTableQueryKey);
            if (String.IsNullOrEmpty(strCommandFromDB))
            {
                InsertQueryCommand(strQueryCommand, strTableName, strTableQueryKey);
                return database.GetSqlStringCommand(strQueryCommand);
            }
            else
            {
                return database.GetSqlStringCommand(strCommandFromDB);
            }            
        }       

        private static string GetWhereClause(string strQueryCommand)
        {
            if (strQueryCommand.Contains("WHERE"))
            {
                return strQueryCommand.Substring(strQueryCommand.IndexOf("WHERE"));
            }
            else
                return string.Empty;
        }

       
        public static string[] GetParameters(string strQueryCommand)
        {
            string[] parameters = new string[0];
            string strWhereClause = GetWhereClause(strQueryCommand);
            if (!string.IsNullOrEmpty(strWhereClause))
            {
                do
                {
                    strWhereClause = strWhereClause.Substring(strWhereClause.IndexOf("@"));
                    string strParameter = strWhereClause.Substring(1, strWhereClause.IndexOf(")") - 1);
                    //string strParameter=strWhereClause.Substring(strWhereClause.IndexOf("@"),
                    Array.Resize(ref parameters, parameters.Length + 1);
                    parameters[parameters.Length - 1] = strParameter;
                    strWhereClause = strWhereClause.Substring(strParameter.Length + 1);
                    if (strWhereClause.StartsWith(")"))
                        strWhereClause = strWhereClause.Substring(1);
                } while (strWhereClause.Contains("@"));

                return parameters;
            }
            else
                return null;
        }

        public static DataSet RunQuery(string strTableName, string strQueryCommandKey, string strQueryCommand, params object[] paramValues)
        {
            TableName = strTableName;
            DbCommand cmd = GetQuery(strTableName, strQueryCommandKey, strQueryCommand);
            string[] parameters = GetParameters(strQueryCommand);
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    if (paramValues[i].GetType().Equals(typeof(int)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.Int, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(bool)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.Bit, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(short)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.SmallInt, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(double)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.Float, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(decimal)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.Decimal, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(String)) || paramValues[i].GetType().Equals(typeof(string)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.NVarChar, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(DateTime)))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.DateTime, paramValues[i]);
                    else if (paramValues[i].GetType().Equals(typeof(byte[])))
                        database.AddInParameter(cmd, parameters[i], SqlDbType.VarBinary, paramValues[i]);
                    else
                        continue;
                }
            }
            return (DataSet)RunQuery(cmd);
        }

        public static DataSet RunQuery(DbCommand cmd)
        {
            return database.ExecuteDataSet(cmd);
        }

        #endregion

        public static String SynchronizeData(String publicationName, String subscriberName, Delegate handler)
        {
            SynchronizationStatus = MessageStatus.None;
            ServerConnection cnn = new ServerConnection(new SqlConnection(_connectionString));
            String msg = String.Empty;
            bool flag = true;
            try
            {
                // Connect to the Publisher.
                cnn.Connect();

                // Instantiate the push subscription.
                TransSubscription subscription = new TransSubscription();
                subscription.ConnectionContext = cnn;
                subscription.DatabaseName = cnn.DatabaseName;
                subscription.PublicationName = publicationName;
                subscription.SubscriptionDBName = cnn.DatabaseName;
                subscription.SubscriberName = subscriberName;

                // If the push subscription exists, start the synchronization.
                if (subscription.LoadProperties() && subscription.AgentJobId != null)
                {
                    // Check that we have enough metadata to start the agent.
                    if (subscription.SubscriberSecurity != null)
                    {
                        if (handler != null)
                        {
                            EventInfo statusEvent = subscription.SynchronizationAgent.GetType().GetEvent("Status");
                            statusEvent.AddEventHandler(subscription.SynchronizationAgent, handler);
                        }
                        // Synchronously start the Distribution Agent for the subscription.
                        subscription.SynchronizationAgent.DistributorSecurityMode = SecurityMode.Standard;
                        subscription.SynchronizationAgent.DistributorLogin = ConfigurationManager.AppSettings["UserID"];
                        subscription.SynchronizationAgent.DistributorPassword = ConfigurationManager.AppSettings["Password"];
                        subscription.SynchronizationAgent.Synchronize();
                    }
                    else
                        flag = false;
                }
                else
                {
                    // Do something here if the subscription does not exist.
                    flag = false;
                }
            }
            catch (Exception ex)
            {
                // Implement appropriate error handling here.
                flag = false;
            }
            finally
            {
                if (flag == false)
                    msg = "There is an unspecified error happening while executing synchronization, maybe because of the Internet connection. Please try again later!"; 
                            
                cnn.Disconnect();
            }
            return msg;
        }
    }
}
